from flask import Flask, render_template
from flask import request
import mysql.connector
import PW       # 数据库相关性信息写在PW.py中
from hashlib import md5

conn = mysql.connector.connect(
    user=PW.my_username, password=PW.my_password, database=PW.database)  # 数据库连接
cursor = conn.cursor()

app = Flask(__name__)  # 引入Flask


@app.route('/', methods=['GET'])  # 跳转至login.html，请求方式GET
def show():
    return render_template('login.html')


@app.route('/login', methods=['POST', 'GET'])
def login():
    if request.method == 'GET':
        return render_template('login.html')
    else:
        user_id = request.form['user_id']  # 界面传值
        password = request.form['password']  # 界面传值
        if(len(user_id) == 0 | len(password) == 0):
            return render_template('login.html')
        password_db = md5(password.encode('utf-8')).hexdigest()
        # 查询test表查询用户名
        cursor.execute(
            'select user_password,user_name from user_info where user_id = %s', (user_id,))
        results = cursor.fetchall()
        if(len(results) == 0):
            return '<h>未找到账号</h><form action="/login" method="get"><p><button type="submit">返回登录</button></p></form>'
        if (password_db == results[0][0]):
            return render_template('user.html', name=results[0][1], user_id=user_id)
        else:
            return '<h>密码错误，请重新登录</h><form action="/login" method="get"><p><button type="submit">返回登录</button></p></form>'
        cursor.close()  # 关闭游标
        conn.close()  # 关闭连接


@app.route('/user', methods=['POST', 'GET'])
def user():
    if request.method == 'GET':
        return render_template('user.html')


@app.route('/regist', methods=['POST', 'GET'])  # 表单提交
def regist():
    if request.method == 'GET':
        return render_template('regist.html')
    else:
        user_id = request.form.get('user_id')
        user_phone = request.form.get('user_phone')
        username = request.form.get('username')
        password = request.form.get('password')
        password_db = md5(password.encode('utf-8')).hexdigest()
        cursor.execute(
            'SELECT * FROM user_info WHERE user_id = %s', (user_id,))
        results = cursor.fetchall()
        if(len(results) > 0):
            return '<h>该id已被占用，请更换id。</h><form action="/regist" method="get"><p><button type="submit">返回注册</botton></p></form>'
        # 把注册信息加入test表
        cursor.execute(
            'INSERT INTO user_info VALUES (%s,%s,%s,%s)', (
                user_id, user_phone, username, password_db)
        )
        conn.commit()
        return '<h>注册成功！请登录。</h><form action="/login" method="get"><p><button type="submit">返回登录</button></p></form>'
        # cursor.close()  # 关闭游标
        # conn.close()  # 关闭连接


@app.route('/sell', methods=['POST', 'GET'])
def sell():
    if request.method == 'GET':
        return render_template('sell.html')
    else:
        seller_id = request.form.get('seller_id')
        position_id = request.form.get('position_id')
        price = request.form.get('price')
        area = request.form.get('area')
        cursor.execute('SELECT * FROM house_info')
        cursor.fetchall()
        house_id = cursor.rowcount + 1
        cursor.execute('INSERT INTO house_info VALUES (%s,%s,%s,%s,%s)',
                       (house_id, position_id, price, area, seller_id))
        conn.commit()
        cursor.execute(
            'SELECT * FROM user_info WHERE user_id = %s', (seller_id,))
        seller_info = cursor.fetchone()
        # cursor.execute(
        #     'INSERT INTO owner_info VALUES (%s,%s,%s)', (seller_info[0], seller_info[1], seller_info[2]))
        # conn.commit()
        return '<h>添加成功！请手动关闭该页面。</h>'


@app.route('/reserve', methods=['POST', 'GET'])
def reserve():
    if request.method == 'GET':
        return render_template('reserve.html')
    else:
        cursor.execute('SELECT * FROM reservation_info')
        cursor.fetchall()
        reservation_id = cursor.rowcount + 1
        user_id = request.form.get('user_id')
        house_id = request.form.get('house_id')
        reservation_time = request.form.get('reservation_time')
        cursor.execute('INSERT INTO reservation_info VALUES (%s,%s,%s,%s)',
                       (reservation_id, user_id, house_id, reservation_time))
        conn.commit()
        return '<h>预约成功！请手动关闭该页面。</h>'


@app.route('/collect', methods=['POST', 'GET'])
def collect():
    if request.method == 'GET':
        return render_template('collect.html')
    else:
        cursor.execute('SELECT * FROM collection_info')
        cursor.fetchall()
        collection_id = cursor.rowcount + 1
        user_id = request.form.get('user_id')
        house_id = request.form.get('house_id')
        cursor.execute('INSERT INTO collection_info VALUES (%s,%s,%s)',
                       (collection_id, user_id, house_id))
        conn.commit()
        cursor.execute(
            'SELECT * FROM user_info WHERE user_id = %s', (user_id,))
        buyer_info = cursor.fetchone()
        # cursor.execute(
        #     'INSERT INTO buyer_info VALUES (%s,%s,%s)', (buyer_info[0], buyer_info[1], buyer_info[2]))
        # conn.commit()
        return '<h>收藏成功！请手动关闭该页面。</h>'


@app.route('/info/<user_id>', methods=['POST', 'GET'])
def info(user_id):
    if request.method == 'GET':
        cursor.execute(
            'SELECT house_info.house_id,user_info.user_name,position_name,price,area FROM collection_info,house_info,position_info,user_info \
            WHERE collection_info.user_id = %s \
            AND house_info.house_id = collection_info.collection_id \
            AND house_info.position_id = position_info.position_no \
            AND user_info.user_id = house_info.seller_id', (user_id,))
        collect_results = cursor.fetchall()
        cursor.execute(
            'SELECT * FROM trade_info WHERE buyer_id=(%s) OR seller_id=(%s)', (user_id, user_id))
        trade_results = cursor.fetchall()
        cursor.execute(
            'SELECT house_id,position_name,price,area FROM house_info,position_info\
            WHERE seller_id=%s \
            AND house_info.position_id=position_info.position_no ', (user_id,))
        house_results = cursor.fetchall()
        cursor.execute(
            'SELECT reservation_id,house_id,reservation_time FROM reservation_info \
            WHERE user_id=%s', (user_id,))
        reserve_results = cursor.fetchall()
        return render_template('info.html',
                               collect_results=collect_results,
                               trade_results=trade_results,
                               house_results=house_results,
                               reserve_results=reserve_results)


@app.route('/hot', methods=['POST', 'GET'])
def hot():
    if request.method == 'GET':
        cursor.execute(
            'SELECT collection_info.house_id,position_name,price,area,count(*) \
            FROM collection_info,house_info,position_info \
            WHERE house_info.house_id=collection_info.house_id \
            AND position_id=position_no \
            GROUP BY house_id \
            ORDER BY count(*) DESC;')
        hot_results = cursor.fetchall()
        return render_template('hot.html', hot_results=hot_results)


@app.route('/search', methods=['POST', 'GET'])
def search():
    if request.method == 'GET':
        return render_template('search.html')
    else:
        area_min = request.form['area_min']
        area_max = request.form['area_max']
        price_min = request.form['price_min']
        price_max = request.form['price_max']
        cursor.execute(
            'SELECT house_id,position_name,price,area,seller_id FROM house_info,position_info \
            WHERE position_id=position_no \
            AND area >= %s \
            AND area <= %s \
            AND price >= %s \
            AND price <= %s', (area_min, area_max, price_min, price_max))
        search_results = cursor.fetchall()
        return render_template('result.html', search_results=search_results)


if __name__ == '__main__':
    app.run()
